
'--------------------------------------------------
' Hands-On 8-1
'--------------------------------------------------
' start indexing array elements at 1
Option Base 1

Sub FavoriteCities()
    ' now declare the array
    Dim cities(6) As String

    ' assign the values to array elements
    cities(1) = "Baltimore"
    cities(2) = "Atlanta"
    cities(3) = "Boston"
    cities(4) = "Washington"
    cities(5) = "New York"
    cities(6) = "Trenton"

    ' display the list of cities
    MsgBox cities(1) & Chr(13) & cities(2) & Chr(13) _
        & cities(3) & Chr(13) & cities(4) & Chr(13) _
        & cities(5) & Chr(13) & cities(6)
End Sub


'--------------------------------------------------
' Hands-On 8-2
'--------------------------------------------------

Sub FavoriteCities2()
    ' now declare the array
    Dim cities(6) As String
    Dim city As Variant

    ' assign the values to array elements
    cities(1) = "Baltimore"
    cities(2) = "Atlanta"
    cities(3) = "Boston"
    cities(4) = "Washington"
    cities(5) = "New York"
    cities(6) = "Trenton"

    ' display the list of cities in separate messages
    For Each city In cities
        MsgBox city
    Next
End Sub


'--------------------------------------------------
' Hands-On 8-3
'--------------------------------------------------

Sub FavoriteCities3()
    ' now declare the array
    Dim cities(6) As String

    ' assign the values to array elements
    cities(1) = "Baltimore"
    cities(2) = "Atlanta"
    cities(3) = "Boston"
    cities(4) = "Washington"
    cities(5) = "New York"
    cities(6) = "Trenton"

    ' call another procedure and pass the array as argument
    Hallo cities()
End Sub


Sub Hallo(cities() As String)
    Dim counter As Integer

    For counter = LBound(cities()) To UBound(cities())
        MsgBox "Hello " & cities(counter)
    Next
End Sub


'--------------------------------------------------
' Hands-On 8-4
'--------------------------------------------------


Sub Lotto()
    Const spins = 6
    Const minNum = 1
    Const maxNum = 54

    Dim t As Integer            ' looping variable in outer loop
    Dim i As Integer            ' looping variable in inner loop
    Dim myNumbers As String     ' string to hold all picks
    Dim lucky(spins) As String  ' array to hold generated picks

    myNumbers = ""

    For t = 1 To spins
        Randomize
        lucky(t) = Int((maxNum - minNum + 1) * Rnd) + minNum

        ' see if this number was drawn before
        For i = 1 To (t - 1)
            If lucky(t) = lucky(i) Then
                lucky(t) = Int((maxNum - minNum + 1) * Rnd) + minNum
                i = 0
            End If
        Next i
        MsgBox "Lucky number is " & lucky(t)
        myNumbers = myNumbers & " - " & lucky(t)
    Next t

    MsgBox "Lucky numbers are " & myNumbers
End Sub


' ---------------------------------------------------
' Hands-On 8-5
' ---------------------------------------------------

Sub Exchange()
    Dim t As String
    Dim r As String
    Dim Ex(3, 3) As Variant

    t = Chr(9)  ' tab
    r = Chr(13) ' Enter

    Ex(1, 1) = "Japan"
    Ex(1, 2) = "Yen"
    Ex(1, 3) = 104.57
    Ex(2, 1) = "Mexico"
    Ex(2, 2) = "Peso"
    Ex(2, 3) = 11.2085
    Ex(3, 1) = "Canada"
    Ex(3, 2) = "Dollar"
    Ex(3, 3) = 1.2028
    MsgBox "Country " & t & t & "Currency" & t & "per US$" _
    & r & r _
        & Ex(1, 1) & t & t & Ex(1, 2) & t & Ex(1, 3) & r _
        & Ex(2, 1) & t & t & Ex(2, 2) & t & Ex(2, 3) & r _
        & Ex(3, 1) & t & t & Ex(3, 2) & t & Ex(3, 3) & r & r _
        & "* Sample Exchange Rates for Demonstration Only", , _
        "Exchange"
End Sub


' ---------------------------------------------------
' Hands-On 8-6
' ---------------------------------------------------

Sub DynArray()
    Dim counter As Integer

    ' declare a dynamic array
    Dim myArray() As Integer

    ' specify the initial size of the array
    ReDim myArray(5)

    Workbooks.Add

    ' populate myArray with values
    For counter = 1 To 5
        myArray(counter) = counter + 1
        ActiveCell.Offset(counter - 1, 0).Value = myArray(counter)
    Next

    ' change the size of myArray to hold 10 elements
    ReDim Preserve myArray(10)

    ' add new values to myArray
    For counter = 6 To 10
        myArray(counter) = counter * counter
        With ActiveCell.Offset(counter - 1, 0)
            .Value = myArray(counter)
            .Font.Bold = True
        End With
    Next counter
End Sub


' ---------------------------------------------------
' Hands-On 8-7
' ---------------------------------------------------

Option Base 1

Sub CarInfo()
    Dim auto As Variant
    auto = Array("Ford", "Black", "1999")
    MsgBox auto(2) & " " & auto(1) & ", " & auto(3)
    auto(2) = "4-door"
    MsgBox auto(2) & " " & auto(1) & ", " & auto(3)
End Sub


Sub ColumnHeads()
    Dim heading As Variant
    Dim cell As Range
    Dim i As Integer

    i = 1
    heading = Array("First Name", "Last Name", "Position", "Salary")
    Workbooks.Add

    For Each cell In Range("A1:D1")
    cell.Formula = heading(i)
    i = i + 1
    Next

    Columns("A:D").Select
    Selection.Columns.AutoFit
    Range("A1").Select
End Sub


' ---------------------------------------------------
' Hands-On 8-8
' ---------------------------------------------------

Sub IsThisArray()
    ' declare a dynamic array
    Dim sheetNames() As String
    Dim totalSheets As Integer
    Dim counter As Integer


    ' count the sheets in the current workbook
    totalSheets = ActiveWorkbook.Sheets.Count

    ' specify the size of the array
    ReDim sheetNames(1 To totalSheets)

    ' enter and show the names of sheets
    For counter = 1 To totalSheets
        sheetNames(counter) = ActiveWorkbook.Sheets(counter).Name
        MsgBox sheetNames(counter)
    Next counter

    ' check if this is indeed an array
    If IsArray(sheetNames) Then
        MsgBox "The sheetNames is an array."
    End If
End Sub


' ---------------------------------------------------
' Hands-On 8-9
' ---------------------------------------------------

' start indexing array elements at 1
Option Base 1

Sub FunCities()
' declare the array
Dim cities(1 To 5) As String

' assign the values to array elements
cities(1) = "Las Vegas"
cities(2) = "Orlando"
cities(3) = "Atlantic City"
cities(4) = "New York"
cities(5) = "San Francisco"

' display the list of cities
    MsgBox cities(1) & Chr(13) & cities(2) & Chr(13) _
        & cities(3) & Chr(13) & cities(4) & Chr(13) _
        & cities(5)
Erase cities

' show all that was erased
   MsgBox cities(1) & Chr(13) & cities(2) & Chr(13) _
       & cities(3) & Chr(13) & cities(4) & Chr(13) _
       & cities(5)
End Sub


' ---------------------------------------------------
' Hands-On 8-10
' ---------------------------------------------------

Sub FunCities2()
    ' declare the array
    Dim cities(1 To 5) As String

    ' assign the values to array elements
    cities(1) = "Las Vegas"
    cities(2) = "Orlando"
    cities(3) = "Atlantic City"
    cities(4) = "New York"
    cities(5) = "San Francisco"

    ' display the list of cities
    MsgBox cities(1) & Chr(13) & cities(2) & Chr(13) _
        & cities(3) & Chr(13) & cities(4) & Chr(13) _
        & cities(5)
    ' display the array bounds
    MsgBox "The lower bound: " & LBound(cities) & Chr(13) _
        & "The upper bound: " & UBound(cities)
End Sub


' ---------------------------------------------------
' Hands-On 8-11
' ---------------------------------------------------

Sub Zoo1()
    ' this procedure triggers an error "Subscript out of range"
    Dim zoo(3) As String
    Dim i As Integer
    Dim response As String
    i = 0

    Do
        i = i + 1
        response = InputBox("Enter a name of animal:")
        zoo(i) = response
    Loop Until response = ""
End Sub


Sub Zoo2()
    ' this procedure avoids the error "Subscript out of range"
    Dim zoo(3) As String
    Dim i As Integer
    Dim response As String
    i = 1

    Do While i >= LBound(zoo) And i <= UBound(zoo)
        response = InputBox("Enter a name of animal:")
        If response = "" Then Exit Sub
        zoo(i) = response
        i = i + 1
    Loop


    For i = LBound(zoo) To UBound(zoo)
        MsgBox zoo(i)
    Next
End Sub


' ---------------------------------------------------
' Hands-On 8-12
' ---------------------------------------------------

Function AddMultipleArgs(ParamArray myNumbers() As Variant)
    Dim mySum As Single
    Dim myValue As Variant
    For Each myValue In myNumbers
        mySum = mySum + myValue
    Next
    AddMultipleArgs = mySum
End Function


' run the above function procedure from the Immediate window
?AddMultipleArgs(1, 23.24, 3, 24, 8, 34)


